﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_Children
/// </summary>
public class DAL_Children : clsConnection
{
    DataTable dt = new DataTable();
    clsQueryList clsquery = new clsQueryList();
    public DataTable DAL_Children_getDataById(int id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children WHERE status = 1 AND id="+id);
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Children_getDK(string id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children WHERE childrenid='" + id + "'");
        }
        catch
        {
        }
        return dt;
    }
    //moi them
    public DataTable DAL_Children_getAllData()
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children");
        }
        catch
        {
        }
        return dt;
    }
    public Boolean DAL_Children_addtodel(int id)
    {
        try
        {
            string sql = "delete from Children where id=" + id;
            clsquery.addSQL(sql);
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_Children_del()
    {
        try
        {
            clsquery.Execute();
        }
        catch
        {
            return false;
        }
        return true;
    }
    public DataTable DAL_Children_getAllByPid(int Pid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children WHERE id = " + Pid);
        }
        catch
        {
        }
        return dt;
    }
    public Boolean DAL_Children_Insert(DTO_Children obj)
    {
        try
        {
            string sql = "INSERT INTO Children(childrenid, fullname, gentle, birthday, address, phone, photo, pass, status) VALUES(@childrenid, @fullname, @gentle, @birthday, @adress, @phone, @photo, @pass, @status)";
            AddParameter(new SqlParameter("@childrenid", obj.Childrenid));
            AddParameter(new SqlParameter("@fullname", obj.Fullname));
            AddParameter(new SqlParameter("@gentle", obj.Gentle));
            AddParameter(new SqlParameter("@birthday", obj.Birthday));
            AddParameter(new SqlParameter("@adress", obj.Adress));
            AddParameter(new SqlParameter("@phone", obj.Phone));
            AddParameter(new SqlParameter("@photo", obj.Photo));
            AddParameter(new SqlParameter("@pass", obj.Pass));
            AddParameter(new SqlParameter("@status", obj.Status));


            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public Boolean DAL_Children_Update(DTO_Children obj)
    {
        try
        {
            string sql = "UPDATE Children SET Fullname = @fullname, Gentle = @gentle, Birthday = @birthday, Address = @adress, Phone = @phone, Photo = @photo, Status = @status WHERE id = @id";
            //AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@fullname", obj.Fullname));
            AddParameter(new SqlParameter("@gentle", obj.Gentle));
            AddParameter(new SqlParameter("@Birthday", obj.Birthday));
            AddParameter(new SqlParameter("@adress", obj.Adress));
            AddParameter(new SqlParameter("@phone", obj.Phone));
            AddParameter(new SqlParameter("@photo", obj.Photo));
            AddParameter(new SqlParameter("@status", obj.Status));
            AddParameter(new SqlParameter("@id", obj.ID));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public DataTable DAL_Children_getSearch(string childrenid, string fullname, string adress)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children WHERE childrenid Like '%" + childrenid + "%' AND fullname LIKE N'%" + fullname + "%' AND address LIKE N'%" + adress + "%'");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_GetChildrenJoinChildrenclassByClassid(string cid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM children inner join childrenclass on children.childrenid = childrenclass.childrenid WHERE classid= '"+ cid+"'");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_Children_Login(string where)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM Children WHERE " + where + "");
        }
        catch
        {
        }
        return dt;
    }
    public Boolean DAL_Children_UpdatePassword(DTO_Children obj)
    {
        try
        {
            string sql = "UPDATE Children SET Pass = @pass WHERE childrenid = @childrenid";
            AddParameter(new SqlParameter("@pass", obj.Pass));
            AddParameter(new SqlParameter("@childrenid", obj.Childrenid));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
}